問題描述
無法恢復 pg_dump 備份 (Unable to restore pg_dump backup)
I'm trying to restore backup from Postgres 9.1.1, which was created by:
pg_dump mydb > backup.sql
restore on Postgres 9.1.9
psql ‑d mydb ‑f backup.sql
I'm getting this error:
psql:datasets.sql:278537: invalid command \.
psql:datasets.sql:278544: ERROR: syntax error at or near "1"
LINE 1: 1 4446 49 253.412262 239.618317 0 211.54303 100.482948 197.1...
The \. is part of COPY command, I guess it should be compatible between Postgres 9 versions, or it's not?
COPY data_136 (id, in_1, in_2, in_3, in_4, out_1) FROM stdin;
1 5.0999999 3.5 1.39999998 0.200000003 Iris‑setosa
2 4.9000001 3 1.39999998 0.200000003 Iris‑setosa
‑‑‑ few more line cutted
150 5.9000001 3 5.0999999 1.79999995 Iris‑virginica
\.
參考解法
方法 1:
The problem was caused by earlier error:
ERROR: permission denied for schema pg_catalog
I was importing the under user who has all privileges to databases, anyway running the import under postgres user solved the issue.
方法 2:
This error occurred to me for the same reason: I was logged in as a user with insufficient permissions.
Just for completeness I'll mention how you can do the restoring whilst specifying the user performing the action:
psql ‑U postgres ‑d mydb ‑f backup.sql